﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using WXYW.Entities;
using System.Data.SqlClient;
using WXYW.DAL;
using WXYW.Model;

namespace WXYW.BLL
{
    public class ExportService : RepositoryBase
    {
        private DataTable GetData(string sqlString)
        {
            string sqlconnectionstring = GetSQLConnectionString("SQLConnectionString");
            DataTable result = new DataTable();
            using (SqlConnection connection = new SqlConnection(sqlconnectionstring))
            {
                SqlCommand command = new SqlCommand(sqlString, connection);
                command.CommandType = CommandType.Text;
                //command.Parameters.AddWithValue("Fields", fileds);
                //command.Parameters.AddWithValue("ProgramGUID", programGuid);
                //command.Parameters.AddWithValue("Days", days);
                //command.Parameters.AddWithValue("Emails", emaillistStr);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(result);
            }

            return result;
        }

        public List<MaintainProduct> GetMaintainProductModel(string starttime, string endtime, string engineerID, string servicestationID, string type, string formType)
        {
            IQueryable<ApplicationProduct> Products = GetEntities<ApplicationProduct>(p => p.ApplicationForm.IsSubmit == true).OrderBy(p => p.ApplicationProductID);

            if (type != "0")
            {
                switch (type)
                {
                    case "1":
                        Products = Products.Where(p => p.ApplicationForm.ReceiveDateTime.HasValue);
                        break;
                    case "2":
                        Products = Products.Where(p => p.RepairCompleteDateTime.HasValue);
                        break;
                    case "3":
                        Products = Products.Where(p => p.SendBackTime.HasValue);
                        break;
                }
            }

            if (!string.IsNullOrEmpty(formType))
            {
                if (!formType.StartsWith("B"))
                {
                    Products = Products.Where(p => p.ApplicationForm.ApplicationType == formType);
                }
                else
                {
                    Products = Products.Where(p => p.ApplicationForm.ApplicationType.StartsWith("B"));
                }
            }
            if (!string.IsNullOrEmpty(starttime))
            {
                DateTime stime = Convert.ToDateTime(starttime);
                switch (type)
                {
                    case "1":
                        Products = Products.Where(p => p.ApplicationForm.ReceiveDateTime.Value > stime);
                        break;
                    case "2":
                        Products = Products.Where(p => p.RepairCompleteDateTime.Value > stime);
                        break;
                    case "3":
                        Products = Products.Where(p => p.SendBackTime.Value >= stime);
                        break;
                    default:
                        Products = Products.Where(p => p.ApplicationForm.ApplicationDateTime.Value > stime);
                        break;
                }
            }

            if (!string.IsNullOrEmpty(endtime))
            {
                DateTime etime = Convert.ToDateTime(endtime + " 23:59:59");

                switch (type)
                {
                    case "1":
                        Products = Products.Where(p => p.ApplicationForm.ReceiveDateTime.Value < etime);
                        break;
                    case "2":
                        Products = Products.Where(p => p.RepairCompleteDateTime.Value < etime);
                        break;
                    case "3":
                        Products = Products.Where(p => p.SendBackTime.Value < etime);
                        break;
                    default:
                        Products = Products.Where(p => p.ApplicationForm.ApplicationDateTime.Value < etime);
                        break;
                }
            }

            if (engineerID != "0")
            {
                int id = Convert.ToInt32(engineerID);
                Products = Products.Where(p => p.User.UserID == id);
            }
            if (servicestationID != "0")
            {
                int ssid = Convert.ToInt32(servicestationID);
                Products = Products.Where(p => p.ApplicationForm.ServiceStation.ID == ssid);
            }

            List<MaintainProduct> productModelList = new List<MaintainProduct>();
            foreach (ApplicationProduct product in Products)
            {
                productModelList.Add(ConvertProductToModel(product));
            }

            return productModelList;
        }

        private MaintainProduct ConvertProductToModel(ApplicationProduct product)
        {
            MaintainProduct maintainProduct = new MaintainProduct();
            if (!product.ApplicationFormReference.IsLoaded)
            {
                product.ApplicationFormReference.Load();
            }
            maintainProduct.RMACode = product.ApplicationForm.ApplicationCode;

            if (!product.ApplicationForm.ServiceStationReference.IsLoaded)
            {
                product.ApplicationForm.ServiceStationReference.Load();
            }
            if (product.ApplicationForm.ServiceStation != null)
            {
                maintainProduct.ServiceStation = product.ApplicationForm.ServiceStation.Name;
            }

            if (!product.ProductTypeReference.IsLoaded)
            {
                product.ProductTypeReference.Load();
            }
            maintainProduct.ProductType = product.ProductType.ProductTypeName;

            if (!product.ProductType.ProductClassReference.IsLoaded)
            {
                product.ProductType.ProductClassReference.Load();
            }
            maintainProduct.ProductClass = product.ProductType.ProductClass.Name;

            if (!product.ApplicationForm.UserReference.IsLoaded)
            {
                product.ApplicationForm.UserReference.Load();
            }
            maintainProduct.ClientConnectPerson = product.ApplicationForm.User.ContactPerson;
            maintainProduct.ClientPhone = product.ApplicationForm.User.UserPhone;
            maintainProduct.ClientCompany = product.ApplicationForm.User.FullName;

            if (product.ApplySpecialService.HasValue && product.ApplySpecialService.Value == true)
            {
                maintainProduct.ISTF = "特服";
            }

            maintainProduct.ProductProperty = GetProductProperty(product.ProductType.ProductClass.ID, product.ProductVersionNumber);
            maintainProduct.SerialNumber = product.ProductSerialNumber;
            maintainProduct.VersionNumber = product.ProductVersionNumber;

            maintainProduct.BuyTime = product.ProductPurchaseDateTime;
            maintainProduct.ApplyTime = product.ApplicationForm.ApplicationDateTime;
            maintainProduct.Appearance = product.ProductAppearance;
            maintainProduct.RepairType = product.ProductRepairType;
            maintainProduct.ReceiveTime = product.ApplicationForm.ReceiveDateTime;
            maintainProduct.ConfirmTime = product.ApplicationForm.RMANOGenerateDateTime;
            maintainProduct.DistributeTime = product.DistributeDateTime;
            maintainProduct.ProblemDescription = product.ProductProblemDescription;
            maintainProduct.BLXX = product.BLXX;
            maintainProduct.BLYY = product.ReasonToProblem;

            if (!product.QuatationForm.IsLoaded)
            {
                product.QuatationForm.Load();
            }
            foreach (QuatationForm form in product.QuatationForm)
            {
                if (string.IsNullOrEmpty(maintainProduct.BLLJ))
                {
                    maintainProduct.BLLJ = form.AccessoryName;
                }
                else
                {
                    maintainProduct.BLLJ += "," + form.AccessoryName;
                }
            }
            if ((string.IsNullOrEmpty(maintainProduct.BLLJ) &&
                !string.IsNullOrEmpty(product.RepairAccessory)) ||
                (!string.IsNullOrEmpty(maintainProduct.BLLJ) && !string.IsNullOrEmpty(product.RepairAccessory) &&
                maintainProduct.BLLJ.Length < product.RepairAccessory.Length + 1))
            {
                string accessoryStr = string.Empty;
                List<string> accessoryList = product.RepairAccessory.Split(new char[] { ';'}, StringSplitOptions.RemoveEmptyEntries).ToList();
                List<string> accessoryList1 = new List<string>();
                foreach (string accessory in accessoryList)
                {
                    bool exists = false;
                    foreach (string accessory1 in accessoryList1)
                    {
                        if (accessory1.Contains(accessory))
                        {
                            exists = true;
                            break;
                        }
                    }
                    if (!exists)
                    {
                        accessoryList1.Add(accessory);
                        accessoryStr += accessory+",";
                    }                    
                }
                if (!string.IsNullOrEmpty(accessoryStr) &&
                    accessoryStr.EndsWith(","))
                {
                    accessoryStr.Remove(accessoryStr.Length - 2);
                }
                maintainProduct.BLLJ = accessoryStr;
            }
            //maintainProduct.BLLJ = product.RepairAccessory;
            maintainProduct.BLWZ = product.AccessoryPlace;
            maintainProduct.MaintainRemark = product.Remark;
            maintainProduct.ReplaceType = product.ChangeType;
            maintainProduct.ReplaceProductType = product.ReplaceProductType;
            maintainProduct.ReplaceSerialNumber = product.ReplaceProductSerialNumber;
            maintainProduct.ReplaceVersionNumber = product.ReplaceProductVersion;
            maintainProduct.RepairCompleteTime = product.RepairCompleteDateTime;
            maintainProduct.NoticeTime = product.NoticeClientDateTime;
            maintainProduct.SendWay = product.SendWay == "A" ? "客户自取" : "快递送货上门";
            maintainProduct.SendDate = product.SendBackTime;
            maintainProduct.ClientReceiveDate = product.ClientReceiveDateTime;
            maintainProduct.FormStatus = product.Status;
            if (product.ApplicationForm.Status == "待付款确认" && product.IsQuotePrice.HasValue && product.IsQuotePrice.Value == true && product.IsDecideToRepair.HasValue && product.IsDecideToRepair.Value == true && (!product.SendBackTime.HasValue))
            {
                maintainProduct.FormStatus = "待付款确认";
            }
            if (product.SendBackTime.HasValue)
            {
                maintainProduct.FormStatus = "已发货待客户确认";
            }
            if (product.ClientReceiveDateTime.HasValue)
            {
                maintainProduct.FormStatus = "客户已确认收货";
            }

            if (!product.ProblemTypeReference.IsLoaded)
            {
                product.ProblemTypeReference.Load();
            }
            maintainProduct.ProblemType = product.ProblemType.Name;
            maintainProduct.Appearance = product.ProductAppearance;

            if (!product.UserReference.IsLoaded)
            {
                product.UserReference.Load();
            }
            if (product.User != null)
            {
                maintainProduct.Engineer = product.User.ContactPerson;
            }

            if (!product.RequisitionFormReference.IsLoaded)
            {
                product.RequisitionFormReference.Load();
            }
            if (product.RequisitionForm != null)
            {
                if (!product.RequisitionForm.LogisticCompanyReference.IsLoaded)
                {
                    product.RequisitionForm.LogisticCompanyReference.Load();
                }
                maintainProduct.RequasitionNumber = product.RequisitionForm.DistributionFormID;
                maintainProduct.DistributeCompany = product.RequisitionForm.LogisticCompany.Name;
                maintainProduct.SendRemark = product.RequisitionForm.Remark;
            }

            if (!product.ProblemAnalyzeReference.IsLoaded)
            {
                product.ProblemAnalyzeReference.Load();
            }
            if (product.ProblemAnalyze != null)
            {
                maintainProduct.proplemAnalyze = GetProblemAnalyze(product.ProblemAnalyze);
            }

            // 备件申请
            if (!product.SparePartApplyForm.IsLoaded)
            {
                product.SparePartApplyForm.Load();
            }
            if (product.SparePartApplyForm.Count > 0)
            {
                SparePartApplyForm spaForm = product.SparePartApplyForm.FirstOrDefault();
                maintainProduct.SSApplyTime = spaForm.ApplyTime;
                if (spaForm.Status.Equals("备件-不同意"))
                {
                    maintainProduct.SSDeclineTime = spaForm.DealWithTime;
                    maintainProduct.SSDeclineReason = spaForm.Reason;
                }
                else
                {
                    maintainProduct.SSApproveTime = spaForm.DealWithTime;
                }
                maintainProduct.SSSendTime = spaForm.SendTime;
                maintainProduct.SSReceiveTime = spaForm.ReceiveTime;
                maintainProduct.SSReceiveRmark = spaForm.Remark;
                maintainProduct.SSReturnTime = spaForm.SendReturnTime;
                maintainProduct.SSReceiveReturnTime = spaForm.ReceiveReturnTime;
                maintainProduct.SSProductType = spaForm.ReturnProductType;
                maintainProduct.SSSerialNumber = spaForm.ReturnProductSerialNumber;
                maintainProduct.SSStatus = spaForm.Status;
            }

            // 升级
            if (!product.UpgradeForm.IsLoaded)
            {
                product.UpgradeForm.Load();
            }
            if (product.UpgradeForm.Count > 0)
            {
                UpgradeForm upform = product.UpgradeForm.Where(u => u.Status != "升级-不同意").FirstOrDefault();
                UpgradeForm rejectForm = product.UpgradeForm.Where(u => u.Status == "升级-不同意").FirstOrDefault();
                maintainProduct.UPStation = "北京";

                if (rejectForm != null)
                {
                    maintainProduct.UPDeclineTime = rejectForm.DealWithTime;
                    maintainProduct.UPDeclineReason = rejectForm.Suggestion;
                    maintainProduct.UPApplyTime = rejectForm.ApplyTime;
                }

                if (upform != null)
                {
                    maintainProduct.UPApplyTime = upform.ApplyTime;
                    maintainProduct.UPApproveTime = upform.DealWithTime;
                    maintainProduct.UPSendTime = upform.SendTime;
                    maintainProduct.UPReceiveTime = upform.ReceiveTime;
                    maintainProduct.UPReceiveRemark = upform.Remark;
                    maintainProduct.UPDistributeTime = upform.DistributeTime;
                    maintainProduct.UPSendBackTime = upform.SendBackTime;
                    maintainProduct.UPStationReceiveTime = upform.CompleteTime;
                    maintainProduct.UPStationReceiveRemark = upform.ServiceStationReceiveRemark;
                }
            }

            // 返厂
            if (!product.ReturnToFactoryForm.IsLoaded)
            {
                product.ReturnToFactoryForm.Load();
            }
            if (product.ReturnToFactoryForm.Count > 0)
            {
                ReturnToFactoryForm rtfForm = product.ReturnToFactoryForm.FirstOrDefault();
                maintainProduct.RFTime = rtfForm.ApplyDate;
                maintainProduct.Factory = GetProvicerName(rtfForm.ProvideCode);
                maintainProduct.CodeConfirmTime = rtfForm.ConfirmCodeDate;
                maintainProduct.RFCode = rtfForm.Code;
                maintainProduct.RFSendTime = rtfForm.SendDate;
                maintainProduct.FactoryReceiveTime = rtfForm.FactoryReceiveDate;
                maintainProduct.FactoryQuatationTime = rtfForm.ProviderQuatationDate;
                maintainProduct.FactoryQuatationReason = rtfForm.QuatationReason;
                maintainProduct.ConfirmFactoryQuatationTime = rtfForm.ConfirmQuatationDate;
                if (rtfForm.DoesRepair.HasValue && rtfForm.DoesRepair.Value == true)
                {
                    maintainProduct.RFSMIsDecideToRepair = "是";
                }
                else if (rtfForm.DoesRepair.HasValue && rtfForm.DoesRepair.Value == false)
                {
                    maintainProduct.RFSMIsDecideToRepair = "否";
                }
                maintainProduct.RFCheckTime = rtfForm.CheckDate;
                maintainProduct.CheckResult = rtfForm.CheckRemark;
                maintainProduct.RFReceiveTime = rtfForm.ReceiveDate;
            }

            // 给客户报价
            maintainProduct.RFQuatationToClientTime = product.QuotationDateTime;
            maintainProduct.RFClientConfirmTime = product.ConfirmQuotationDateTime;
            //if (product.IsDecideToRepair.HasValue && product.IsDecideToRepair == true)
            //{
            //    maintainProduct.RFIsDecideToRepair = "是";
            //}
            if (product.IsDecideToRepair.HasValue && product.IsDecideToRepair == false)
            {
                maintainProduct.RFIsDecideToRepair = "否";
            }
            else
            {
                maintainProduct.RFIsDecideToRepair = "是";
            }

            if (!product.QuatationForm.IsLoaded)
            {
                product.QuatationForm.Load();
            }
            double cost = 0;
            foreach (QuatationForm qform in product.QuatationForm)
            {
                if (qform.IsBWBA.HasValue && qform.IsBWBA.Value == true)
                {
                    maintainProduct.ISBWBA = "保外备案";
                }
                else
                {
                    cost += qform.Cost;
                }
            }

            maintainProduct.Cost = cost;

            if (!product.ApplicationForm.GatheringReference.IsLoaded)
            {
                product.ApplicationForm.GatheringReference.Load();
            }
            if (product.ApplicationForm.Gathering != null)
            {
                if (product.IsQuotePrice.HasValue && product.IsQuotePrice.Value == true && product.IsDecideToRepair.HasValue && product.IsDecideToRepair.Value == true)
                {
                    maintainProduct.GatheringTime = product.ApplicationForm.Gathering.PayDateTime;
                    maintainProduct.GatheringRemark = product.ApplicationForm.Gathering.Remark;
                }
            }
            maintainProduct.KPTime = product.ApplicationForm.KPDateTime;

            if (!product.ApplicationForm.FeedbackReference.IsLoaded)
            {
                product.ApplicationForm.FeedbackReference.Load();
            }
            if (product.ApplicationForm.Feedback != null)
            {
                maintainProduct.MaintainQuality = product.ApplicationForm.Feedback.MaintainQuality;
                maintainProduct.Period = product.ApplicationForm.Feedback.MaintainPeriod;
                maintainProduct.Respose = product.ApplicationForm.Feedback.ServiceResponse;
                maintainProduct.Comment = GetComment(product.ApplicationForm.Feedback);
            }

            // 返厂周期 = （返厂返回日期BZ-返厂日期BN）-（收费确认日期BW-工厂收费报价日期BV）
            if (maintainProduct.RFTime != null && maintainProduct.RFReceiveTime != null)
            {
                if (maintainProduct.FactoryQuatationTime != null && maintainProduct.ConfirmFactoryQuatationTime != null)
                {
                    maintainProduct.RF = GetDays(maintainProduct.RFTime.Value, maintainProduct.RFReceiveTime.Value) - GetDays(maintainProduct.FactoryQuatationTime.Value, maintainProduct.ConfirmFactoryQuatationTime.Value);
                }
                else
                {
                    maintainProduct.RF = GetDays(maintainProduct.RFTime.Value, maintainProduct.RFReceiveTime.Value);
                }                
            }


            if (maintainProduct.RFQuatationToClientTime != null && maintainProduct.GatheringTime != null)
            {
               //收费确认周期 = 给客户报价日期 - 收费到款日期 - 1
               maintainProduct.SF = GetDays(maintainProduct.RFQuatationToClientTime.Value, maintainProduct.GatheringTime.Value);                
            }
            else if ((maintainProduct.RFQuatationToClientTime != null && maintainProduct.GatheringTime == null) ||
                (maintainProduct.RFQuatationToClientTime == null && maintainProduct.GatheringTime != null))
            {
                maintainProduct.SF = 8888;
            }

            // 升级周期 = 下升级接收时间 BL - 升级申请时间 BC - 1
            if (maintainProduct.UPApplyTime != null && maintainProduct.UPStationReceiveTime != null)
            {
                maintainProduct.SJ = GetDays(maintainProduct.UPApplyTime.Value, maintainProduct.UPStationReceiveTime.Value);
            }

            // 备件/器件申请周期 = 备件申请AP-维修站收到备件时间AU-1
            if (maintainProduct.SSApplyTime != null && maintainProduct.SSReceiveTime != null)
            {
                maintainProduct.BJ = GetDays(maintainProduct.SSApplyTime.Value, maintainProduct.SSReceiveTime.Value);
            }
            // 升级审批发货周期 = 升级申请时间BC-升级发出时间BG-1
            if (maintainProduct.UPSendTime.HasValue && maintainProduct.UPApplyTime.HasValue)
            {
                maintainProduct.SJSPFH = GetDays(maintainProduct.UPApplyTime.Value, maintainProduct.UPSendTime.Value);
            }
            // 物流平台-北京=升级发出BG-升级收到BH-1
            if (maintainProduct.UPReceiveTime != null && maintainProduct.UPSendTime != null)
            {
                maintainProduct.WLCToB = GetDays(maintainProduct.UPSendTime.Value, maintainProduct.UPReceiveTime.Value);
            }
            // 物流北京-平台=下升级发出时间BK-下升级接受时间BL-1
            if (maintainProduct.UPSendBackTime != null && maintainProduct.UPStationReceiveTime != null)
            {
                maintainProduct.WLBToC = GetDays(maintainProduct.UPSendBackTime.Value, maintainProduct.UPStationReceiveTime.Value);
            }
            // 物流-客户=发货日期AL-客户收机日期AN-1
            if (maintainProduct.ClientReceiveDate != null && maintainProduct.SendDate != null)
            {
                maintainProduct.WLCToC = GetDays(maintainProduct.SendDate.Value, maintainProduct.ClientReceiveDate.Value);
            }

            //根据AI列，如果AI为
            //"快递送货上门"
            //A: 客户收机日期AN-收机时间P-收费确认周期
            //"客户自取"
            //B：发货通知日期AH-收机日期 P-收费确认周期
            if (product.SendWay == "A") // 客户自取
            {
                if (maintainProduct.ReceiveTime.HasValue && maintainProduct.NoticeTime.HasValue)
                {
                    maintainProduct.TAT = GetDays(maintainProduct.ReceiveTime.Value, maintainProduct.NoticeTime.Value);
                    if (maintainProduct.SF > 0 && maintainProduct.SF != 8888)
                    {
                        maintainProduct.TAT = maintainProduct.TAT - maintainProduct.SF - 1;
                    }
                }
            }
            else //快递送货上门
            {
                if (maintainProduct.ReceiveTime.HasValue && maintainProduct.ClientReceiveDate.HasValue)
                {
                    maintainProduct.TAT = GetDays(maintainProduct.ReceiveTime.Value, maintainProduct.ClientReceiveDate.Value);
                    if (maintainProduct.SF > 0 && maintainProduct.SF != 8888)
                    {
                        maintainProduct.TAT = maintainProduct.TAT - maintainProduct.SF - 1;
                    }
                }
            }

            if (maintainProduct.SF < 0)
            {
                maintainProduct.SF = 0;
            }

            maintainProduct.DealWithDateTime = product.DealWithDateTime;
            maintainProduct.BJXXClientReceive = product.BJXXClientReceive;
            maintainProduct.BJXXReturnBack = product.BJXXReturnBack;
            maintainProduct.BJXXSendToClient = product.BJXXSendToClient;
            return maintainProduct;
        }

        private int GetDays(DateTime startDate, DateTime endDate)
        {
            int days = 0;
            for (DateTime dt = startDate; dt.Date < endDate.Date; dt = dt.AddDays(1))
            {
                if (dt.DayOfWeek != DayOfWeek.Saturday && dt.DayOfWeek != DayOfWeek.Sunday)
                {
                    days++;
                }
            }
            return days;
        }

        private string GetComment(Feedback feedback)
        {
            string comment = string.Empty;
            if (!string.IsNullOrEmpty(feedback.MaintainQualityComment))
            {
                comment += "," + feedback.MaintainQualityComment;
            }
            if (!string.IsNullOrEmpty(feedback.MaintainPeriodComment))
            {
                comment += "," + feedback.MaintainPeriodComment;
            }
            if (!string.IsNullOrEmpty(feedback.ServiceResponseComment))
            {
                comment += "," + feedback.ServiceResponseComment;
            }

            if (!string.IsNullOrEmpty(comment))
            {
                comment = comment.Substring(1);
            }

            return comment;
        }

        private string GetProvicerName(string code)
        {
            string name = string.Empty;
            ProviderService service = new ProviderService();
            Provider provider = service.GetProviderByCode(code);
            if (provider != null)
            {
                name = provider.ProviderName;
            }

            return name;
        }

        private string GetProblemAnalyze(ProblemAnalyze problemAnalyze)
        {
            string analyze = string.Empty;
            if (!string.IsNullOrEmpty(problemAnalyze.A))
            {
                analyze += "," + problemAnalyze.A;
            }
            if (!string.IsNullOrEmpty(problemAnalyze.B))
            {
                analyze += "," + problemAnalyze.B;
            }
            if (!string.IsNullOrEmpty(problemAnalyze.C))
            {
                analyze += "," + problemAnalyze.C;
            }
            if (!string.IsNullOrEmpty(problemAnalyze.D))
            {
                analyze += "," + problemAnalyze.D;
            }
            if (!string.IsNullOrEmpty(problemAnalyze.E))
            {
                analyze += "," + problemAnalyze.E;
            }

            if (!string.IsNullOrEmpty(analyze))
            {
                analyze = analyze.Substring(1);
            }

            return analyze;
        }

        private string GetProductProperty(int classID, string versionNumber)
        {
            string property = "自研";
            Product pu = GetEntities<Product>(p => p.ProductClass.ID == classID && p.VersionNumber == versionNumber).FirstOrDefault();

            if (pu != null)
            {
                property = pu.Property;
            }

            return property;
        }

        public DataTable GetProduct(string starttime, string endtime, string engineerID, string servicestationID, string type, string formType)
        {
            string sqlstr = GetSQLStringByKey(ExportKey.MaintainRecordString.ToString());
            if (type != "0")
            {
                string wherestr = string.Empty;
                switch (type)
                {
                    case "1":
                        wherestr = " and dbo.ApplicationForm.ReceiveDateTime is not null";
                        break;
                    case "2":
                        wherestr = " and dbo.ApplicationProduct.RepairCompleteDateTime is not null";
                        break;
                    case "3":
                        wherestr = " and dbo.ApplicationProduct.SendBackTime is not null";
                        break;
                }

                sqlstr += wherestr;
            }
            if (!string.IsNullOrEmpty(formType))
            {
                sqlstr += string.Format(" and dbo.ApplicationForm.ApplicationType = '{0}'", formType);
            }
            if (!string.IsNullOrEmpty(starttime))
            {
                string exportType = "dbo.ApplicationForm.ApplicationDateTime";
                switch (type)
                {
                    case "1":
                        exportType = "dbo.ApplicationForm.ReceiveDateTime";
                        break;
                    case "2":
                        exportType = "dbo.ApplicationProduct.RepairCompleteDateTime";
                        break;
                    case "3":
                        exportType = "dbo.ApplicationProduct.SendBackTime";
                        break;
                }
                sqlstr += string.Format(" and {0} > '{1} 00:00:00'", exportType, starttime);
            }
            if (!string.IsNullOrEmpty(endtime))
            {
                string timestring = "dbo.ApplicationForm.ApplicationDateTime";
                switch (type)
                {
                    case "1":
                        timestring = "dbo.ApplicationForm.ReceiveDateTime";
                        break;
                    case "2":
                        timestring = "dbo.ApplicationProduct.RepairCompleteDateTime";
                        break;
                    case "3":
                        timestring = "dbo.ApplicationProduct.SendBackTime";
                        break;
                }
                sqlstr += string.Format(" and {0} < '{1} 23:59:59'", timestring, endtime);
            }
            if (engineerID != "0")
            {
                sqlstr += string.Format(" and engineer.UserID={0}", engineerID);
            }
            if (servicestationID != "0")
            {
                sqlstr += string.Format(" and dbo.ApplicationForm.ServiceStationID={0}", servicestationID);
            }

            DataTable oriDT = GetData(sqlstr);
            List<string> list = new List<string>();
            if (oriDT.Rows.Count > 0)
            {
                foreach (DataColumn col in oriDT.Rows[0].Table.Columns)
                {
                    list.Add(col.ColumnName);
                }
            }
            DataView view = GetData(sqlstr).DefaultView;

            DataTable dt = view.ToTable(true, list.ToArray());
            return dt;
        }

        public DataTable ExportProductInformation()
        {
            string sqlStr = "SELECT p.[MaterialID] as 物料号,p.[ModelNumber] as 产品型号,p.[VersionNumber] as 版本号 ,c.Name as 产品类型,p.[Property] as 产品属性,p.[RepairPeriod] as 保修期限,p.[Description] as 产品描述 FROM [dbo].[Product] p";
            sqlStr += " LEFT JOIN dbo.ProductClass c ON p.ProductClassID = c.ID";

            return GetData(sqlStr);
        }

        public DataTable ExprotProvider()
        {
            string sqlStr = "SELECT [ProviderCode] as 代理商代码,[ProviderName] as 代理商名称,c.Name as 产品类型,[Relation] as 关系,[City] as 所属城市,[Postcode] as 邮编,[Address] as 通讯地址";
            sqlStr += ",[Contact1Name] as 接口人1,[Contact1Phone] as 联系电话,[Contact1MobilePhone] as 联系手机,[Contact1Fax] as 联系传真,[Contact1Email] as 邮箱";
            sqlStr += ",[Contact2Name] as 接口人2,[Contact2Phone] as 联系电话,[Contact2MobilePhone] as 联系手机,[Contact2Fax] as 联系传真,[Contact2Email] as 邮箱";
            sqlStr += " FROM [dbo].[Provider] p LEFT JOIN dbo.ProductClass c ON p.ProductClassID = c.ID";

            return GetData(sqlStr);
        }

        public DataTable ExportClient()
        {
            string sqlStr = "SELECT c.Name as OEM产品类型,u.[UserName] as 用户ID,u.[FullName] as 公司全称,u.[CompanyName] as 公司简称,u.[ContactPerson] as 联系人,u.[UserPhone] as 联系电话,u.[UserMobilePhone] as 联系手机,u.[UserFax] as 联系传真,u.[UserEmail] as 电子邮件,u.[UserAddress] as 通讯地址 ,u.[Comment] as 备注";
            sqlStr += " FROM [dbo].[User] u LEFT JOIN dbo.ProductClass c ON u.[OEMProductClassID] = c.ID where UserType = 10";

            return GetData(sqlStr);
        }

        public DataTable ExportSparePart(string stationID)
        {
            string sqlStr = "SELECT s.[MaterialNO] as 物料号,s.[ModelNumber] as 产品型号 ,s.[VersionNumber] as 版本号,c.Name as 产品分类,s.[Count] as 数量,s.[MarketPrice] as 市场价格,s.[SparePartPrice] as 备件价格,s.[StockType] as 库存类型,s.[GoodCount] as 良品,s.[BadCount] as 不良品,t.Name as 入库地";
            sqlStr += " FROM [dbo].[SparePart] s LEFT JOIN dbo.ProductClass c ON s.ProductTypeID = c.ID LEFT JOIN dbo.ServiceStation t ON t.ID = s.[EnterStationID] where 1=1";
            if (stationID != "0")
            {
                sqlStr += string.Format(" and s.[EnterStationID]={0}", stationID);
            }

            return GetData(sqlStr);
        }

        public DataTable ExportAccessory(string stationID)
        {
            string sqlStr = "SELECT a.[MaterialNO] as 物料号,a.[ModelNO] as 原料型号,a.[Description] as 原料描述,c.Name as 原料分类,a.[Price] as 收费价格,a.[Count] as 数量,a.[CBPrice] as 成本价格,t.Name as 平台";
            sqlStr += " FROM [dbo].[Accessory] a LEFT JOIN dbo.AccessoryClass c ON a.[ClassID] = c.ID LEFT JOIN dbo.ServiceStation t ON t.ID = a.[EnterStationID] where 1=1";

            if (stationID != "0")
            {
                sqlStr += string.Format(" and a.[EnterStationID]={0}", stationID);
            }

            return GetData(sqlStr);
        }

        public DataTable ExportReturnToFactoryForms(string starttime, string endtime)
        {
            string sqlStr = GetSQLStringByKey(ExportKey.ReturnToFactoryString.ToString());
            if (!string.IsNullOrEmpty(starttime))
            {
                sqlStr += string.Format(" and r.ApplyDate > '{0} 00:00:00'", starttime);
            }
            if (!string.IsNullOrEmpty(endtime))
            {
                sqlStr += string.Format(" and r.ApplyDate < '{0} 23:59:59'", endtime);
            }

            return GetData(sqlStr);
        }

        public DataTable ExportSparePartApplyForms(string starttime, string endtime)
        {
            string sqlStr = GetSQLStringByKey(ExportKey.SparePartApplyString.ToString());
            if (!string.IsNullOrEmpty(starttime))
            {
                sqlStr += string.Format(" and s.ApplyTime > '{0} 00:00:00'", starttime);
            }
            if (!string.IsNullOrEmpty(endtime))
            {
                sqlStr += string.Format(" and s.ApplyTime < '{0} 23:59:59'", endtime);
            }

            return GetData(sqlStr);
        }

        public DataTable ExportUpgradeFroms(string starttime, string endtime)
        {
            string sqlStr = GetSQLStringByKey(ExportKey.UpgradeString.ToString());
            if (!string.IsNullOrEmpty(starttime))
            {
                sqlStr += string.Format(" and s.ApplyTime > '{0} 00:00:00'", starttime);
            }
            if (!string.IsNullOrEmpty(endtime))
            {
                sqlStr += string.Format(" and s.ApplyTime < '{0} 23:59:59'", endtime);
            }

            return GetData(sqlStr);
        }

        private string GetSQLStringByKey(string key)
        {
            string str = string.Empty;
            SqlString sqlstr = GetEntities<SqlString>(s => s.Key == key).FirstOrDefault();
            if (sqlstr != null)
            {
                str = sqlstr.SqlString1;
            }

            return str;
        }

        public DataTable ExportTasks(string startTime, string endTime, int serviceStationID)
        {
            string sqlStr = string.Format( "SELECT [Subject] AS 主题" +
                            ", U1.ContactPerson AS 执行人" +
                            ", ISNULL(U2.ContactPerson, U2.UserName) AS 创建人" +
                            ",[CreateDate] AS 创建日期" +
                            ",[ShouldCompleteDate] AS 预计完成时间" +
                            ",[CompleteDate] AS 实际完成时间" +
                            ",[Status] AS 状态" +
                            ",[Content] AS 内容" +
                            ",[Result] AS 结果" +
                            ",[TaskType] AS 任务类型" +
                            ",[ReasonType] AS 问题类型"+
                            ",[ResultType] AS 问题原因类型" +
                            ",[Feedback] AS 回访信息"+
                            ",[FeedbackDate] AS 回访时间 "+
                            "FROM [Task] LEFT JOIN [User] U1 ON Task.AssignToPerson = U1.UserID " +
                            "LEFT JOIN [User] U2 ON Task.CreatePerson = U2.UserID "+
                            "WHERE CreateDate > '{0}' AND CreateDate < '{1}' ",
                            startTime, endTime);

            if (serviceStationID > 0)
            {
                sqlStr += string.Format(" AND U1.ServiceStationID = {1}", serviceStationID);
            }

            return GetData(sqlStr);
        }
    }
}
